const query = require("../db/query");

//  获取医生 包含 工号 姓名 性别 年龄 科室 职位 手机的模糊搜索
const Getdoctorlist = async (params) => {
    // 倒序 desc  正序 asc
    let sql = `select * from doctorlist where doctorid LIKE '%${params.doctorid}%' and doctorname like '%${params.doctorname}%'and doctorgender like '%${params.doctorgender}%'and doctorage like '%${params.doctorage}%'and doctoradministre like '%${params.doctoradministre}%'and doctorstate like '%${params.doctorstate}%'and doctorphone like '%${params.doctorphone}%' ORDER BY doctorid desc`
    return await query(sql)
}

// 获取医生所属的大科室
const Getadmin = async () => {
    let sql = `select * from admin`
    return await query(sql)
}

// 获取中国各省数据
const Getprovince = async () => {
    let sql = `select * from china where pid=0`
    return await query(sql)
}

// 获取中国省份下的市
const Getcity = async (params) => {
    let sql = `SELECT * FROM china WHERE Pid = (SELECT Id FROM china WHERE Name = '${params.name}');`
    return await query(sql)
}

// 获取中国市下的县
const Getcounty = async (params) => {
    let sql = `SELECT * FROM china WHERE Pid = (SELECT Id FROM china WHERE Name = '${params.name}');`
    return await query(sql)
}

// 多表联查 通过医生所属的大科室的id 获取医生所属的小科室
const Getadminchildred = async (params) => {
    let sql = `SELECT adminchildred.* FROM admin INNER JOIN adminchildred ON admin.id = adminchildred.adminchildrentel WHERE admin.id = ${params.id};`
    return await query(sql)
}

// 获取医生的职位
const Getposition = async () => {
    let sql = `select * from position`
    return await query(sql)
}

// 添加医生
const Adddoctorlist = async (params) => {
    let sql = `INSERT INTO doctorlist (doctorname,doctorgender,doctorage,doctordescword,doctoradministre,doctorcity,doctorimage,doctorstate,doctoradmin,doctorprice,doctoriddoc,doctorgood,doctorbrowse,doctortime,doctorflag,doctorphone) VALUES ('${params.doctorname}','${params.doctorgender}','${params.doctorage}', '该条为医生的介绍', '${params.doctoradministre}','${params.doctorcity}','${params.doctorimage}','${params.doctorstate}','3698','${params.doctorprice}','131121200001013456','${params.doctorgood}','666','300','1','${params.doctorphone}');`;
    return await query(sql)
}

// 添加医生后,根据医生所属的科室增加响应科室的医生的数量
const Addadminchildrenstaff = async () => {
    let sql = `UPDATE adminchildred SET adminchildrenstaff = adminchildrenstaff + 1 WHERE adminchildrenname = (SELECT doctoradministre FROM doctorlist ORDER BY doctorid DESC LIMIT 1);`
    return await query(sql)
}

// 删除医生
const Deldoctorlist = async (params) => {
    let sql = `DELETE FROM doctorlist WHERE doctorid = ${params.doctorid};`;
    return await query(sql)
}

// 删除医生后,根据医生所属的科室减少响应科室的医生的数量
const Reduceadminchildrenstaff = async () => {
    let sql = `UPDATE adminchildred SET adminchildrenstaff = adminchildrenstaff - 1 WHERE adminchildrenname = (SELECT doctoradministre FROM doctorlist ORDER BY doctorid DESC LIMIT 1);`
    return await query(sql)
}

// 修改医生
const Updateddoctorlist = async (params) => {
    let sql = `UPDATE doctorlist SET doctorname='${params.doctorname}',doctorage = ${params.doctorage},doctorgender='${params.doctorgender}',doctorcity='${params.doctorcity}' ,doctorprice='${params.doctorprice}',doctorgood='${params.doctorgood}',doctorphone='${params.doctorphone}' WHERE doctorid = ${params.doctorid}`
    return await query(sql)
}

module.exports = {
    Getdoctorlist,//获取医生
    Getadmin,//获取医生所属的大科室
    Getadminchildred,//获取医生所属的小科室
    Getposition,//获取医生职位
    Getprovince,// 获取中国省份
    Getcity,// 获取中国省份下的城市
    Getcounty,// 获取中国省份下的城市下的区县
    Adddoctorlist,//添加医生
    Addadminchildrenstaff,//添加医生后根据医生所属的科室,增加科室的人数
    Updateddoctorlist,//修改医生
    Deldoctorlist,//删除医生
    Reduceadminchildrenstaff,//删除医生后根据医生所属的科室,减少科室的人数
}